/*
 * OrderBroker.java
 *
 * Created on 17 de agosto de 2008, 03:52 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package flexbrokers;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import listeners.InitApp;
import model.Customer;
import model.LineItem;
import model.Order;
import model.Product;
import model.Salesman;

/**
 *
 * @author Alirio
 */
public class OrderBroker {
  
  /** Creates a new instance of OrderBroker */
  public OrderBroker() {
  }
  
  public Order getOrder(long id) {
    java.sql.Connection cnn = (java.sql.Connection) InitApp.getServletContext().getAttribute("cnn");
    java.sql.Statement stmt;
    Order order = null;
    try {
      stmt = cnn.createStatement();
      java.sql.ResultSet rs =stmt.executeQuery("select orders.*, customers.name, salesmen.name as name2 from orders,customers,salesmen where orders.customer_id = customers.customer_id and orders.salesman_id=salesmen.salesman_id and orders.order_id=" + id);
      while (rs.next()) {
        order = new Order();
        order.setId(rs.getLong("order_id"));
        order.setDate(rs.getString("order_date"));
        order.setCustomer(new Customer(rs.getString("customer_id"), rs.getString("name")));
        order.setSalesman(new Salesman(rs.getString("salesman_id"), rs.getString("name2")));
        order.setStatus(rs.getString("status"));
        order.setTime(rs.getString("order_time"));
        order.setComments(rs.getString("comments"));
        order.setAmount(rs.getDouble("amount"));
        order.setRequiredDate(rs.getString("required_date"));
        order.setStatusChangedAt(rs.getString("status_changed_at"));
        order.setStatusChangedOn(rs.getString("status_changed_on"));
        java.sql.Statement stmt2 = cnn.createStatement();
        java.sql.ResultSet rs2 = stmt2.executeQuery("select orders_details.*, products.description, products.alternative_unit, products.conversion_rate from orders_details, products where orders_details.product_id=products.product_id and order_id=" + order.getId());
        
        while (rs2.next()) {
          Product p = new Product("", rs2.getString("description"));
          p.setAlternativeUnit(rs2.getString("alternative_unit"));
          p.setConversionRate(rs2.getFloat("conversion_rate"));
          LineItem li = new LineItem(p , rs2.getDouble("qty"), rs2.getString("unit"), false);
          li.setSubtotal(rs2.getDouble("subtotal"));
          order.addProduct(li);
        }
        rs2.close();
        stmt2.close();
        
        rs.close();
        stmt.close();
      }
    } catch (SQLException ex) {
      ex.printStackTrace();
    }
    
    return order;
  }
  
  public boolean deleteOrder(String id) {
    java.sql.Connection cnn = (java.sql.Connection) InitApp.getServletContext().getAttribute("cnn");
    java.sql.Statement stmt;
    boolean result = true;
    try {
        stmt = cnn.createStatement();
        long ar = stmt.executeUpdate("delete from orders where order_id=" + id);
        if (ar==0) result =false;
        ar = stmt.executeUpdate("delete from orders_details where order_id=" + id);
        if (ar==0) result =false;
        stmt.close();
      } catch (SQLException ex) {
        result = false;
        ex.printStackTrace();
      }
    return result;
  }
  
}
